import sqlite3

def create_tables():
    conn = sqlite3.connect('question_bank.db')
    cursor = conn.cursor()

    # 创建课程表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS courses (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL UNIQUE
    )
    ''')

    # 检查课程表是否创建成功
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='courses';")
    if cursor.fetchone():
        print("Courses table created successfully.")
    else:
        print("Failed to create courses table.")

    # 创建知识点表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS knowledge_points (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        name TEXT NOT NULL,
        course_id INTEGER,
        FOREIGN KEY (course_id) REFERENCES courses(id)
    )
    ''')

    # 检查知识点表是否创建成功
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='knowledge_points';")
    if cursor.fetchone():
        print("Knowledge points table created successfully.")
    else:
        print("Failed to create knowledge points table.")

    # 创建题目表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS questions (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        course_id INTEGER,
        knowledge_point_id INTEGER,
        question_type TEXT NOT NULL,
        content TEXT NOT NULL,
        options TEXT,
        answer TEXT NOT NULL,
        explanation TEXT,
        difficulty REAL NOT NULL,
        FOREIGN KEY (course_id) REFERENCES courses(id),
        FOREIGN KEY (knowledge_point_id) REFERENCES knowledge_points(id)
    )
    ''')

    # 检查题目表是否创建成功
    cursor.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='questions';")
    if cursor.fetchone():
        print("Questions table created successfully.")
    else:
        print("Failed to create questions table.")

    conn.commit()
    conn.close()

if __name__ == '__main__':
    create_tables()
